package security.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.Properties;

import util.CommonUtils;
import util.Constants;

public class DbMgr {
	private String dbname;
	private String dbUrl;
	private String dbDriver;	
	private String dbUser;
	private String dbPass;
	
	private static DbMgr dbMgr = null;
	
	private DbMgr() {
		init();
	}
	
	public static DbMgr getInstance() {
		if(dbMgr == null)
			dbMgr = new DbMgr();
		return dbMgr;
	}
	
	private void init() {
		Properties properties = CommonUtils.loadPropertiesFile("db.properties", null);
		dbDriver = properties.getProperty("db.driver");
		dbname = properties.getProperty("db.name");
		dbUrl = properties.getProperty("db.url");
		dbUser = properties.getProperty("db.user");
		dbPass = properties.getProperty("db.password");
		
		// This will load the MySQL driver, each DB has its own driver
		try {
			Class.forName(dbDriver).newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public String getDbname() {
		return dbname;
	}

	public ResultSet executeQuery(Connection connection, String query, String queryType) {
		if(query != null && connection != null) {
			return executeQuery(query, queryType, connection);
		}
		return null;
	}
	
	public ResultSet executeQuery(Statement statement, String query, String queryType) {
		if(query != null && statement != null) {
			return executeQuery(query, queryType, statement);
		}
		return null;
	}
	
	public int executeInsertQuery(Connection connection, String query, String queryType, Map<String, Object> params){
		if(query != null && connection != null) {
			try {
				Statement statement = connection.createStatement();
				if (queryType.equalsIgnoreCase(DbQuery.INSERT_QUERY)) {
					statement.executeUpdate(query);
					ResultSet rs = statement.getGeneratedKeys();
					if(rs.next())
						return rs.getInt(1);
				} 
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		return -1;
	}
	
	public int executeInsertQuery(Statement statement, String query, String queryType, Map<String, Object> params){
		if(query != null && statement != null) {
			try {
				if (queryType.equalsIgnoreCase(DbQuery.INSERT_QUERY)) {
					statement.executeUpdate(query);
					ResultSet rs = statement.getGeneratedKeys();
					if(rs.next())
						return rs.getInt(1);
				} 
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		return -1;
	}
	
	
	public void closeConnection(Connection connection) {
		if(connection != null) {
			try {
				connection.close(); 
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public Connection createConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(dbUrl+dbname,dbUser,dbPass);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}
	
	public Statement createConnectionStatement(Connection conn) {
		try {
			Statement statement = conn.createStatement();
			return statement;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	private ResultSet executeQuery(String query, String queryType, Statement statement) {		
		ResultSet rs = null;
		try {
			if(queryType.equalsIgnoreCase(DbQuery.SELECT_QUERY)) {
				rs = statement.executeQuery(query);				
			} else if (queryType.equalsIgnoreCase(DbQuery.INSERT_QUERY) || queryType.equalsIgnoreCase(DbQuery.UPDATE_QUERY)) {
				statement.executeUpdate(query);	
			} else if (queryType.equalsIgnoreCase(DbQuery.DELETE_QUERY)) {
				statement.executeUpdate(query);
			}
		} catch (SQLException e) {
			System.out.println(query);
			e.printStackTrace();
		}
		return rs;
	}
	
	private ResultSet executeQuery(String query, String queryType, Connection connection) {		
		ResultSet rs = null;
		try {
			Statement statement = connection.createStatement();
			if(queryType.equalsIgnoreCase(DbQuery.SELECT_QUERY)) {
				rs = statement.executeQuery(query);				
			} else if (queryType.equalsIgnoreCase(DbQuery.INSERT_QUERY) || queryType.equalsIgnoreCase(DbQuery.UPDATE_QUERY)) {
				statement.executeUpdate(query);	
			} else if (queryType.equalsIgnoreCase(DbQuery.DELETE_QUERY)) {
				statement.executeUpdate(query);
			}
		} catch (SQLException e) {
			System.out.println(query);
			e.printStackTrace();
		}
		return rs;
	}
	
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		final DbMgr other = (DbMgr) obj;		
		if (dbname == null) {
			if (other.dbname != null)
				return false;
		} else if (!dbname.equals(other.dbname))
			return false;
		return true;
	}

	public void setDbName(String dbName) {
		if(dbName != null) {
			this.dbname = new String(dbName);
		}
	}
	
	public void initDatabase() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(dbUrl+"mysql",dbUser,dbPass);
			String query = "DROP DATABASE IF EXISTS " + dbname;
			executeQuery(query, DbQuery.INSERT_QUERY, connection);
			
			query = "CREATE DATABASE IF NOT EXISTS " + dbname;
			executeQuery(query, DbQuery.INSERT_QUERY, connection);
			
			executeQuery(getSubjectCreationSQL(), DbQuery.UPDATE_QUERY, connection);
			executeQuery(getObjectCreationSQL(), DbQuery.UPDATE_QUERY, connection);
			executeQuery(getAccessCreationSQL(), DbQuery.UPDATE_QUERY, connection);
			executeQuery(getCompanyCreationSQL(), DbQuery.UPDATE_QUERY, connection);
			executeQuery(getCOICreationSQL(), DbQuery.UPDATE_QUERY, connection);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private String getSubjectCreationSQL() {
		String createTableSql = "CREATE TABLE " + dbname + "." + Constants.SUBJECT_TABLE + " ( ";
		createTableSql += ("id VARCHAR(30) NOT NULL, ");
		createTableSql += ("type SMALLINT(10) UNSIGNED, ");
		createTableSql += ("PRIMARY KEY(id)");
		createTableSql += ") ";
		return createTableSql;
	}
	
	private String getObjectCreationSQL() {
		String createTableSql = "CREATE TABLE " + dbname + "." + Constants.OBJECT_TABLE + " ( ";
		createTableSql += ("id VARCHAR(30) NOT NULL, ");
		createTableSql += ("subID VARCHAR(30), ");
		createTableSql += ("type SMALLINT(10) UNSIGNED, ");
		createTableSql += ("content VARCHAR(200), ");
		createTableSql += ("PRIMARY KEY(id), ");
		createTableSql += ("FOREIGN KEY (subID) REFERENCES "+ dbname + "." + Constants.SUBJECT_TABLE+"(id) ON UPDATE CASCADE ON DELETE CASCADE ");
		createTableSql += ") ";
		return createTableSql;
	}
	
	private String getAccessCreationSQL() {
		String createTableSql = "CREATE TABLE " + dbname + "." + Constants.ACCESS_TABLE + " ( ";
		createTableSql += ("subID VARCHAR(30) NOT NULL, ");
		createTableSql += ("objID VARCHAR(30) NOT NULL, ");
		createTableSql += ("PRIMARY KEY(subID, objID), ");
		createTableSql += ("FOREIGN KEY (subID) REFERENCES "+ dbname + "." + Constants.SUBJECT_TABLE+"(id) ON UPDATE CASCADE ON DELETE CASCADE, ");
		createTableSql += ("FOREIGN KEY (objID) REFERENCES "+ dbname + "." + Constants.OBJECT_TABLE+"(id) ON UPDATE CASCADE ON DELETE CASCADE ");
		createTableSql += ") ";
		return createTableSql;
	}
	
	private String getCompanyCreationSQL() {
		String createTableSql = "CREATE TABLE " + dbname + "." + Constants.COMPANY_TABLE + " ( ";
		createTableSql += ("subID VARCHAR(30) NOT NULL, ");
		createTableSql += ("compID VARCHAR(30) NOT NULL, ");
		createTableSql += ("since SMALLINT(10) UNSIGNED, ");
		createTableSql += ("PRIMARY KEY(subID, compID), ");
		createTableSql += ("FOREIGN KEY (subID) REFERENCES "+ dbname + "." + Constants.SUBJECT_TABLE+"(id) ON UPDATE CASCADE ON DELETE CASCADE, ");
		createTableSql += ("FOREIGN KEY (compID) REFERENCES "+ dbname + "." + Constants.SUBJECT_TABLE+"(id) ON UPDATE CASCADE ON DELETE CASCADE ");
		createTableSql += ") ";
		return createTableSql;
	}
	
	private String getCOICreationSQL() {
		String createTableSql = "CREATE TABLE " + dbname + "." + Constants.COI_TABLE + " ( ";
		createTableSql += ("coiID VARCHAR(30) NOT NULL, ");
		createTableSql += ("compID VARCHAR(30) NOT NULL, ");
		createTableSql += ("PRIMARY KEY(coiID, compID), ");
		createTableSql += ("FOREIGN KEY (compID) REFERENCES "+ dbname + "." + Constants.SUBJECT_TABLE+"(id) ON UPDATE CASCADE ON DELETE CASCADE ");
		createTableSql += ") ";
		return createTableSql;
	}
}